package com.example.shoppinglist;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.example.shoppinglist.DatabaseHandler.ShoppingItemsTable;
import com.example.shoppinglist.DatabaseHandler.ShoppingListTable;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class ShoppingListDataSource {

	private SQLiteDatabase database;
	private DatabaseHandler dbHelper;

	// setting up the data base and relevant methods
	public ShoppingListDataSource(Context context) {
		dbHelper = new DatabaseHandler(context);
	}

	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	public void close() {
		dbHelper.close();
	}

	public LinkedHashMap<Integer, ShoppingList> getShoppingLists() {
		//getting the tables and doing a left outer join
		//saving url as well to help speed up app
		open();
		LinkedHashMap<Integer, ShoppingList> shoppingListsMap = new LinkedHashMap<Integer, ShoppingList>();

		String sql = "SELECT l." + ShoppingListTable.COLUMN_NAME_LIST_ID + " , "
				+ "i." + ShoppingItemsTable.COLUMN_NAME_POSITION + " , "
				+ "i." + ShoppingItemsTable.COLUMN_NAME_NAME + " , "
				+ "i." + ShoppingItemsTable.COLUMN_NAME_VALUE + " , " 
				+ "i." + ShoppingItemsTable.COLUMN_NAME_URL + " , "
				+ "l." + ShoppingListTable.COLUMN_NAME_FUNDS + " , " 
				+ "i." + ShoppingItemsTable._ID + " , " 
				+ "l." + ShoppingListTable.COLUMN_NAME_COLOUR + " , " 
				+ "l." + ShoppingListTable.COLUMN_NAME_DATE + " , "
				+ "l." + ShoppingListTable.COLUMN_NAME_CURRENCY + " , " 
				+ "l." + ShoppingListTable.COLUMN_NAME_LIST_TITLE 
				+ " FROM " + ShoppingListTable.TABLE_NAME 
				+ " l LEFT OUTER JOIN " + ShoppingItemsTable.TABLE_NAME 
				+ " i ON l." + ShoppingListTable.COLUMN_NAME_LIST_ID 
				+ " = i." + ShoppingItemsTable.COLUMN_NAME_LIST_ID
				+" ORDER BY i." + ShoppingItemsTable.COLUMN_NAME_POSITION 
				+ " DESC";

		// making cursor to go through the data and save it to a variable
		String[] arr = new String[0];
		Cursor cursor = database.rawQuery(sql, arr);
		cursor.moveToFirst();

		while (!cursor.isAfterLast()) {
			Integer listId = cursor.getInt(0);

			int shoppingItemId = cursor.getInt(6);
			if (!shoppingListsMap.containsKey(listId)) {
				BigDecimal funds = new BigDecimal(cursor.getString(5));
				int colour = cursor.getInt(7);
				long date = cursor.getLong(8);
				String currency = cursor.getString(9);
				String listTitle = cursor.getString(10);
				List<ShoppingItem> items = new ArrayList<ShoppingItem>();
				if (!cursor.isNull(6)) {
					BigDecimal cost = new BigDecimal(cursor.getString(3));
					String url = cursor.getString(4);
					ShoppingItem item = new ShoppingItem(cursor.getString(2), cost, url);
					item.setId(shoppingItemId);
					items.add(item);
				}
				//putting list into map to make getting the data easier
				ShoppingList shoppingList = new ShoppingList(listId,
						funds, colour, new Date(date), currency, listTitle, items);
				shoppingListsMap.put(listId, shoppingList);
			} else {
				ShoppingList shoppingList = shoppingListsMap.get(listId);
				BigDecimal cost = new BigDecimal(cursor.getString(3));
				String url = cursor.getString(4);
				ShoppingItem item = new ShoppingItem(cursor.getString(2), cost, url);
				item.setId(shoppingItemId);
				shoppingList.addItem(item);
			}
			cursor.moveToNext();
		}
		// make sure to close the cursor
		cursor.close();
		close();
		return shoppingListsMap;
	}

	public void update(LinkedHashMap<Integer, ShoppingList> allLists) {
		open();
		String[] string = new String[0];
		database.delete(ShoppingListTable.TABLE_NAME, null, string);
		database.delete(ShoppingItemsTable.TABLE_NAME,null, string);
		for(ShoppingList shoppingList: allLists.values()){
			ContentValues shoppingListValues = new ContentValues();
			shoppingListValues.put(ShoppingListTable.COLUMN_NAME_LIST_ID,
					shoppingList.getListId());
			shoppingListValues.put(ShoppingListTable.COLUMN_NAME_FUNDS,
					shoppingList.getFunds().toString());
			shoppingListValues.put(ShoppingListTable.COLUMN_NAME_COLOUR,
					shoppingList.getColour());
			shoppingListValues.put(ShoppingListTable.COLUMN_NAME_DATE, shoppingList
					.getDate().getTime());
			shoppingListValues.put(ShoppingListTable.COLUMN_NAME_CURRENCY, shoppingList
					.getCurrency());
			shoppingListValues.put(ShoppingListTable.COLUMN_NAME_LIST_TITLE,
					shoppingList.getListName());
			database.insert(ShoppingListTable.TABLE_NAME, null, shoppingListValues);
			int pos =0;
			for(ShoppingItem shoppingItem: shoppingList.getItems()){
				ContentValues shoppingItemValues = new ContentValues();
				shoppingItemValues.put(ShoppingItemsTable.COLUMN_NAME_NAME,
						shoppingItem.getName());
				shoppingItemValues.put(ShoppingItemsTable.COLUMN_NAME_POSITION, pos);
				shoppingItemValues.put(ShoppingItemsTable.COLUMN_NAME_URL, shoppingItem.getUrl());
				shoppingItemValues.put(ShoppingItemsTable.COLUMN_NAME_LIST_ID, shoppingList.getListId());
				shoppingItemValues.put(ShoppingItemsTable.COLUMN_NAME_VALUE,
						shoppingItem.getCost().toString());

				long itemId = database.insert(ShoppingItemsTable.TABLE_NAME, null,
						shoppingItemValues);
				shoppingItem.setId(itemId);
				
				pos++;
			}
		}
		close();
	}
}
